package com.feng.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.StringUtils;

import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 * 
 * @author Feng Jianli
 */
public class ExcelReader {
	
	public static JSONArray readExcel(InputStream ins) throws Exception{
            // 创建需要批量插入数据集合
			JSONArray list = new JSONArray();
            // 创建对Excel工作簿文件的引用
            Workbook wookbook = WorkbookFactory.create(ins);

           
            // 在Excel文档中，第一张工作表的缺省索引是0
            Sheet sheet = wookbook.getSheetAt(0);
            // 获取到Excel文件中的所有行数
            int rows = sheet.getPhysicalNumberOfRows();
            //从第一行获取标题
            Row row = sheet.getRow(0);
            int columnCount = row.getLastCellNum();
            ArrayList<String> keys = new ArrayList<String>();
            for (int j =0 ;j<columnCount;j++){
            	keys.add(getCellString(row.getCell(j)).toString());
        	}
            
            // 遍历行 从第二行开始遍历
            for (int i = 1; i < rows; i++) {
                // 读取左上端单元格
                Row data = sheet.getRow(i);
                // 行不为空
                if (data != null) {
                	//获得本行中单元格的个数
                	JSONObject obj = new JSONObject();
                	for (int j =0,size =keys.size();j<size;j++){
                		obj.put(keys.get(j), getCellString(data.getCell(j)));
                	}
		          list.add(obj);
	            }
            }
	            // 返回集合
          return list;
    }

	/**
	 * 获得单元格中的内容
	 * 
	 * @param cell
	 * @return
	 */
	private static Object getCellString(Cell cell) {
		Object result = null;
		if (cell != null) {
			int cellType = cell.getCellType();
			switch (cellType) {
			case Cell.CELL_TYPE_STRING:
				result = cell.getRichStringCellValue().getString();
				break;
			case Cell.CELL_TYPE_NUMERIC:
				//判断是否为日期类型
				if(HSSFDateUtil.isCellDateFormatted(cell)){
				//用于转化为日期格式
					Date d = cell.getDateCellValue();
					DateFormat formater = new SimpleDateFormat("yyyy/MM/dd");
					result = formater.format(d);
				}else{
					result = cell.getNumericCellValue();
				}
				break;
			case Cell.CELL_TYPE_FORMULA:
				result = cell.getNumericCellValue();
				break;
			case Cell.CELL_TYPE_ERROR:
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				result = cell.getBooleanCellValue();
				break;
			case Cell.CELL_TYPE_BLANK:
				break;
			}
		}
		return result;
	}

	public static String getString(Cell cell) {
		Object o = getCellString(cell);
		if(cell!=null && cell.getCellType()== Cell.CELL_TYPE_NUMERIC){
			if(HSSFDateUtil.isCellDateFormatted(cell)){
				//用于转化为日期格式
				Date d = cell.getDateCellValue();
				DateFormat formater = new SimpleDateFormat("yyyy/MM/dd");
				return formater.format(d);
			}else{
				//result = cell.getNumericCellValue();
				DecimalFormat format = new DecimalFormat("#");
				return format.format(o);
			}
		}
		if(o==null){
			return "";
		}else{
			return o.toString().trim();
		}
	}

	public static Double getDouble(Cell cell) {
		Object o = getCellString(cell);
		if(StringUtils.isEmpty(o)){
			return null;
		}else{
			String str = o.toString();
			if(str.trim().equals("")){
				return null;
			}
			return Double.parseDouble(str);
		}
	}

	public static void setDate(Cell cell,Date date) {
		DateFormat formater = new SimpleDateFormat("yyyy/MM/dd");
		String result = formater.format(date);
		cell.setCellValue(result);
	}

	public static boolean isEqual(Double a,Double b){
		return a!=null&& b!=null && Math.abs(a-b)<=0.03001;
	}

	public static boolean isEqual(String a,String b){
		return a!=null&& b!=null && a.equalsIgnoreCase(b);
	}

	public static String replaceLast(String string, String toReplace, String replacement) {
		int pos = string.lastIndexOf(toReplace);
		if (pos > -1) {
			return string.substring(0, pos)
					+ replacement
					+ string.substring(pos + toReplace.length(), string.length());
		} else {
			return string;
		}
	}

	public static boolean hasTitle(Row row,String title){
		for(int i=0;i<row.getLastCellNum();i++){
			String cellValue = row.getCell(i).getStringCellValue();
			if(cellValue!=null && cellValue.indexOf(title)>-1){
				return true;
			}
		}
		return false;
	}

	public static void main(String[] args) throws Exception {
		/*File file = new File("z:/billTmpl.xls");
		InputStream inputStream = new FileInputStream(file);
		JSONArray arrays = ExcelReader.readExcel(inputStream);
		System.out.println(arrays);*/

		System.out.println("A'ABC_*123".replaceAll("\\W",""));

	}



}
